<?php
/*
 * @ControllerAnnotation(title="客户公海")
 */

namespace app\crm\controller\customer;

use app\common\controller\CrmController;
use app\common\service\BaseService;
use app\crm\model\CrmCustomer;
use app\crm\traits\Curd;
use app\crm\validate\customer\Customer;
use Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Db;
use think\exception\ValidateException;
use think\facade\Env;

class OpenSea extends CrmController
{
    protected $validate = 'customer\Customer';
    protected $model = 'CrmCustomer';

    use Curd;

    /**
     * 需要过滤导入的字段
     * @var string[]
     */
    protected $filter_column = ['id', 'deal_status', 'deal_time', 'is_lock', 'delete_time', 'create_time', 'update_time', 'delete_time', 'collect_time'];

    public function initialize()
    {
        parent::initialize();
        if( in_array($this->request->action(),['import']) ){
            $this->assign('customerOwnerList', BaseService::getCustomerOwnerList());
        }
    }

    /**
     * @NodeAnotation(title="列表")
     */    
    public function index()
    {
        if ($this->request->isAjax()) {
            if (input('selectFields')) {
                return $this->selectList();
            }
            $filter_w['owner_user_id'] = 0;
            list($page, $limit, $where) = $this->buildTableParames();
            $count = $this->model::with('level,industry,source,createUser,ownerUser')
                ->where($where)
                ->where($filter_w)
                ->count();
            $list = $this->model::with('level,industry,source,createUser,ownerUser')
                ->where($where)
                ->where($filter_w)
                ->withAttr('mobile',function($value, $data){
                    return preg_replace('/(^1\d{2})\d{4}(\d{4})/','$1****$2',$value);
                })
                ->page($page, $limit)
                ->order($this->sort)
                ->select();
            $data = [
                'code'  => 0,
                'msg'   => '',
                'count' => $count,
                'data'  => $list,
            ];
            return json($data);
        }
        return $this->fetch();
    }

    /**
     * @NodeAnotation(title="批量导入")
     */    
    public function import()
    {
        if ($this->request->isPost()) {
            $post = $this->request->post();
            if( empty($post['excel']) ){
                return $this->error('excel文件不能为空');
            }
            $filePath = Env::get('root_path') . 'public' . DS . $post['excel'];
            if (!is_file($filePath)) {
                return $this->error('excel文件不存在');
            }
            $ext = pathinfo($filePath, PATHINFO_EXTENSION);
            if (!in_array($ext, ['xlsx'])) {
                return $this->error('注意使用系统准备好的模板填充后进行导入');
            }

            $table = Env::get('database.prefix') . 'crm_customer';
            $database = Env::get('database.database');
            $fieldArr = [];
            $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
            foreach ($list as $k => $v) {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            }

            // 准备读取excel
            $insert = 0;
            $fail_num = 0;
            $fail_row = [];
            try {
                $reader = IOFactory::createReader('Xlsx');
                $reader->setReadDataOnly(TRUE);
                $spreadsheet = $reader->load($post['excel']); //载入Xlsx表格
                $currentSheet = $spreadsheet->getSheet(0);  //读取文件中的第一个工作表
                $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
                $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
                $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);

                $fields = [];
                for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                        $fields[] = trim($val);//去掉左右空格
                    }
                }
                $i = 0;
                $owner_user_ids = explode(',',$post['select']);
                $admin_count = count($owner_user_ids);//待分配员工数量
                for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                    $values = [];
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                        $values[] = is_null($val) ? '' : trim($val);
                    }
                    $row = [];
                    $temp = array_combine($fields, $values);
                    foreach ($temp as $k => $v) {
                        if (isset($fieldArr[$k]) && $k !== '') {
                            if (in_array($fieldArr[$k], $this->filter_column)) continue;
                            $row[$fieldArr[$k]] = trim($v);
                            $row['level_id'] = 2;
                            $row['industry_id'] = 1;
                        }
                    }
                    //分配客户
                    if ((!isset($row['owner_user_id']) || $row['owner_user_id'] == '') && $owner_user_ids){
                        if($post['status'] == 2){  // 随机分配
                            $admin_id = $owner_user_ids[mt_rand(0, $admin_count - 1)];
                        }else if($post['status'] == 1){  // 平均分配
                            $admin_id = $owner_user_ids[$i];
                            $i == ($admin_count - 1) ? $i = 0 : $i++;
                        }
                        $row['owner_user_id'] = $admin_id;
                    }
                    $row['create_user_id'] = session('admin.id');

                    $model = new CrmCustomer();
                    if ($row) {
                        $row['collect_time'] = time();
                        $validate = new Customer();
                        if (!$validate->scene('import')->check($row)) {
                            $row['error'] = $validate->getError();
                            $fail_row[] = $row;
                            $fail_num++;
                        }else{
                            $result = $model->allowField(true)->save($row);
                            if ($result) {
                                $insert++;
                            }
                        }
                        
                    }
                }
            } catch (\Exception $exception) {
                $this->error($exception->getMessage() . "行数：" . $currentRow);
            } catch (ValidateException $e) {
                $this->error($e->getMessage() . "行数：" . $currentRow);
            }

            if ($fail_num) {
                $name = md5(time() . session('admin.id'));
                $list = action('crm/tpl/getFieldInfoByTable',['tabname' => 'crm_customer']);
                $errpath = ($this->createFailRowsExcel($list,$fail_row, $name));
            }
            $return = [
                'insert' => $insert, 
                'fail_num' => $fail_num, 
                'errpath' => isset($errpath) ? $errpath : ''
            ];
            $this->success("成功导入{$insert}条,失败{$fail_num}条",'',$return);
        }
        $this->view->engine->layout(false);
        return $this->fetch();
    }

    /**
     * 生成失败excel
     * @MethodAuthor: 聂哥
     * @Date: 2023-01-14 13:30:23
     */
    private function createFailRowsExcel($list,$data, $batch)
    {
        $pathname = Env::get('root_path') . "/public/data/excel/";
        $path = $pathname . $batch . ".xls";
        if (is_file($path)) {
            return $path;
        }
        if (!is_dir($pathname)) {
            mkdir($pathname, 0755, true);
        }

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $i = 0;
        $fieldArr = [];
        foreach ($list as $k => $v) {
            if (in_array($v['COLUMN_NAME'], $this->filter_column)) continue;
            if ($i >= 26) {
                $cell = chr(65 + $i / 26 - 1) . chr(65 + $i % 26);
            } else {
                $cell = chr(65 + $i);
            }
            $sheet->setCellValue($cell . '1', $v['COLUMN_COMMENT']);
            $i++;
            $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
        }
        $fieldArr['错误']="error";

        $j = 0;
        foreach ($data as $row){
            $i = 0;
            foreach ($fieldArr as $k => $v) {
                if ($i >= 26) {
                    $cell = chr(65 + $i / 26 - 1) . chr(65 + $i % 26);
                } else {
                    $cell = chr(65 + $i);
                }
                if ($v && isset($row[$v])) {
                    $sheet->setCellValue($cell . ($j + 2), $row[$v]);

                }
                $i++;
            }
            $j++;
        }
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save($path);
        return "/data/excel/" . $batch . ".xls";;
    }

    /**
     * 领取
     */
    public function receive()
    {
        $ids = $this->request->param('id');
        if (empty($ids)) {
            return $this->error('缺少id参数');
        }
       
        $success_num = 0;//成功领取的数量
        $error_num = 0;//失败数量
        $error_ids = "";//失败的ids
        $msg = "";//返回消息

        Db::startTrans();
        try {
            $list = $this->model->where('id', 'in', $ids)->lock(true)->select();
            if (!$list) {
                throw new Exception('无效的请求参数id');
            }
            foreach ($list as $row) {
                if ($row['owner_user_id']) {
                    $error_num++;
                    $error_ids = $error_ids ? $error_ids . "," . $row->id : $row->id;
                    continue;
                }
                
                $row->owner_user_id = session('admin.id');
                $row->follow_time = $row->collect_time = time();
                if($row->save()){
                    $success_num++;
                }
            }   
            Db::commit(); 
            $msg = "成功领取{$success_num}条\n失败{$error_num}\n失败ids:{$error_ids}";
            return ['msg'=>$msg,'code'=>1];
        } catch (\Throwable $th) {
            Db::rollback();
            return ['msg'=>'领取失败','code'=>0];
        }
    }

    

}